from sqlalchemy import create_engine
HOSTNAME = '172.16.61.221'
PORT = '3306'
DATABASE = 'mydb'
USERNAME ='jhd'
PASSWORD = 'jihui@duo'

#engine=create_engine('mysql+pymysql://root:qwe123@localhost:3306/test',echo=True)
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
print(DB_URI)
engine = create_engine(DB_URI)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(engine)

from sqlalchemy import Column,Integer,String,Text,Boolean,Date,DateTime,Float
from datetime import datetime,date
class Students(Base):
    __tablename__ = 'students'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(100),nullable=False)
    gender = Column(Boolean)
    admission_time = Column(DateTime,default=datetime.now)
    birthdate = Column(Date)
    article = Column(Text)

    def __repr__(self):
        return '<Students(id="%s",name="%s",gender="%s",admission_time="%s",birthdate="%s",article="%s")>' % (self.id, self.name, self.gender,self.admission_time,self.birthdate,self.article)


Base.metadata.create_all()
from sqlalchemy.orm import  sessionmaker
Session = sessionmaker(engine)
session = Session()
stu1 = Students(name='lily',gender=False,birthdate=date(1999,12,12),article='python')
session.add(stu1)
session.commit()
# rs = session.query(Students).all()
# rs1 = session.query(Students).first()
rs2 = session.query(Students).filter(Students.id==1).all()
print (rs2)






